﻿using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace ecomerci
{
    class applicationLogic
    {
        sqlite sql;

        public string invoicePath { get; set; }
        public string salesOverviewPath { get; set; }

        public applicationLogic()
        {
            sql = new sqlite();
        }

        public void loadSettings()
        {
            if (getInvoicePath() == "")
            {
                if (Directory.Exists(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Rechnungen") == false)
                {
                    Directory.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Rechnungen");
                }
                setInvoicePath((Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Rechnungen").ToString());
            }

            if (getSalesOverviewPath() == "")
            {
                if (Directory.Exists(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Umsatzuebersichten") == false)
                {
                    Directory.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Umsatzuebersichten");
                }
                setSalesOverviewPath((Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ecomerci\Umsatzuebersichten").ToString());
            }

            invoicePath = getInvoicePath();
            salesOverviewPath = getSalesOverviewPath();
        }

#region SQL Interactions

        public customer getCustomer(int Customer_id)
        {
            customer selectedCustomer;
            DataTable datatable = sql.select("SELECT * FROM customers WHERE customer_id = " + Customer_id + ";");
            selectedCustomer = new customer(datatable.Rows[0][1].ToString() + " " + datatable.Rows[0][2].ToString(), datatable.Rows[0][3].ToString(), datatable.Rows[0][4].ToString(), datatable.Rows[0][5].ToString(), datatable.Rows[0][7].ToString(), datatable.Rows[0][6].ToString());
            return selectedCustomer;            
        }

        public void setInvoicePath(string NewPath)
        {
            try
            {
                sql.execute("UPDATE settings SET invoicePath = '" + NewPath.ToString() + "' WHERE id = 1;");
            }
            catch(Exception e)
            {
                showException(e);
            }
        }

        public void setSalesOverviewPath(string NewPath)
        {
            try
            {
                sql.execute("UPDATE settings SET salesOveviewPath = '" + NewPath.ToString() + "' WHERE id = 1;");
            }
            catch(Exception e)
            {
                showException(e);
            }
        }

        public string getInvoicePath()
        {
            DataTable path = sql.select("SELECT invoicePath FROM settings WHERE id = 1;");
            return path.Rows[0][0].ToString();
        }

        public string getSalesOverviewPath()
        {
            DataTable path = sql.select("SELECT salesOveviewPath FROM settings WHERE id = 1;");
            return path.Rows[0][0].ToString();
        }

        public customer getCustomer(string Ebay_username)
        {
            DataTable datatable = sql.select("SELECT * FROM customers WHERE ebay_username = '" + Ebay_username + "';");
            customer selectedCustomer = new customer(datatable.Rows[0][1].ToString() + " " + datatable.Rows[0][2].ToString(), datatable.Rows[0][3].ToString(), datatable.Rows[0][4].ToString(), datatable.Rows[0][5].ToString(), datatable.Rows[0][7].ToString(), datatable.Rows[0][6].ToString());
            return selectedCustomer;
        }

        public transaction getTransaction(int Offer_number)
        {
            DataTable datatable = sql.select("SELECT * FROM transactions WHERE offer_number = " + Offer_number + ";");
            transaction transaction = new transaction(datatable.Rows[0][1].ToString(), datatable.Rows[0][2].ToString(), datatable.Rows[0][3].ToString(), datatable.Rows[0][4].ToString(), datatable.Rows[0][7].ToString(), datatable.Rows[0][5].ToString(), datatable.Rows[0][6].ToString());
            return transaction;
        }

        public DataTable getTransactionDatabase()
        {
            return sql.select("SELECT offer_number, offer_title, date FROM transactions ORDER BY transaction_id DESC;");
        }

        public DataTable getCustomerDatabase()
        {
            return sql.select("SELECT first_name, last_name, ebay_username FROM customers;");
        }

        public int getCustomerId(string Ebay_username)
        {
            DataTable customer = sql.select("SELECT customer_id FROM customers WHERE ebay_username = '" + Ebay_username + "';");

            return int.Parse(customer.Rows[0][0].ToString());
        }

        public DataTable getTransactionByMonth(string[] DateRange)
        {
            return sql.select("SELECT date, offer_number, offer_title, price, purchase_price FROM transactions WHERE date > " + DateRange[0] + " AND date < " + DateRange[1] + ";");
        }

        public string getShipping(string ShippingId)
        {
            int shippingId = int.Parse(ShippingId);
            DataTable shipping = new DataTable();
            shipping = sql.select("SELECT service_name, packet_size FROM shipping WHERE shipping_id = " + shippingId + ";");
            return shipping.Rows[0][0].ToString() + " " + shipping.Rows[0][1].ToString();
        }

        public double getShippingPrice(string ShippingId)
        {
            int shippingId = int.Parse(ShippingId);
            DataTable shipping = new DataTable();
            shipping = sql.select("SELECT price FROM shipping WHERE shipping_id = " + shippingId + ";");
            return double.Parse(shipping.Rows[0][0].ToString());
        }

#endregion

        public void validate(TextBox Textbox, Label Label, CancelEventArgs e)
        {
            if (Textbox.Enabled == true && Textbox.Text == string.Empty)
            {
                Label.ForeColor = Color.Red;
                setBallonTip(Textbox, "Leeres Feld", "Dieses Feld muss ausgefüllt werden.");
                e.Cancel = true;
            }
            else
            {
                Label.ForeColor = Color.Black;
            }
        }

        public void createBackup()
        {
            try
            {
                if (Directory.Exists(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\ecomerci") == false)
                {
                    Directory.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\ecomerci");
                }

                File.Copy(Application.StartupPath + @"\ecomerci.db", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\ecomerci\ecomerci.db_", true);
            }
            catch(FileNotFoundException e)
            {
                showException(e);
            }
        }

        public void setBallonTip(Control Control, string Title, string Text)
        {
            ToolTip bubble = new ToolTip();
            bubble.IsBalloon = true;
            bubble.ToolTipTitle = Title;
            bubble.UseAnimation = true;
            bubble.UseFading = true;
            bubble.ShowAlways = false;
            bubble.SetToolTip(Control, Text);
            bubble.ToolTipIcon = ToolTipIcon.Error;
            bubble.Show(Text, Control, 1500);
        }

        public void showException(Exception e)
        {
            MessageBox.Show(e.Message + "\n" + e.InnerException, "Problem", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
        }

        public string[] getDateRange(int MonthIndex, int Year)
        {
            int monthIndex = MonthIndex + 1;
            int lastMonthDay = 0;
            string zero = "0";
            string[] dateRange = new string[2];

            try
            {
                if (monthIndex == 1 || monthIndex == 3 || monthIndex == 5 || monthIndex == 7 || monthIndex == 8 || monthIndex == 10 || monthIndex == 12)
                {
                    lastMonthDay = 31;
                }
                else
                {
                    lastMonthDay = 30;
                }

                if (monthIndex > 10)
                {
                    zero = "";
                }

                string dateRangeStart = Year + zero + monthIndex + "01";
                string dateRangeEnd = Year + zero + monthIndex + lastMonthDay;

                dateRange[0] = dateRangeStart;
                dateRange[1] = dateRangeEnd;
            }

            catch (Exception e)
            {
                showException(e);
            }

            return dateRange;
        }

#region Form Calls

        public void callPreferences()
        {
            DialogResult dr = new DialogResult();
            preferences pref = new preferences();
            dr = pref.ShowDialog();

            switch (dr)
            {
                case DialogResult.OK:
                    setInvoicePath(pref.invoicePath);
                    setSalesOverviewPath(pref.salesOverviewPath);
                    break;

                case DialogResult.Cancel:
                    pref.Close();
                    break;
            }
        }

        public void callPdf(string PdfFile)
        {
            DialogResult dr = new DialogResult();
            showPDF pdfForm = new showPDF();
            pdfForm.openPDF(PdfFile);
            dr = pdfForm.ShowDialog();

            switch (dr)
            {
                case DialogResult.OK:
                    pdfForm.Hide();
                    break;

                case DialogResult.Cancel:
                    pdfForm.Close();
                    break;
            }
        }

        public void callNewTransaction()
        {
            DialogResult dr = new DialogResult();
            newTransactions addTransactionsForm = new newTransactions();
            dr = addTransactionsForm.ShowDialog();

            switch (dr)
            {
                case DialogResult.OK:
                    
                    break;

                case DialogResult.Cancel:
                    addTransactionsForm.Close();
                    break;
            }
        }

        public void callNewTransaction(transaction Transaction)
        {
            DialogResult dr = new DialogResult();
            newTransactions editTransaction = new newTransactions();
            customer Customer = getCustomer(Transaction.customer_id);
            editTransaction.fillForm(Transaction, Customer);
            dr = editTransaction.ShowDialog();

            switch (dr)
            {
                case DialogResult.OK:
                    customer customerUpdate = editTransaction.customer;
                    transaction transactionUpdate = editTransaction.transaction;

                    string transactionQuery = "UPDATE transactions SET ";
                    transactionQuery += "offer_number = '" + transactionUpdate.offer_number + "', offer_title = '" + transactionUpdate.offer_title + "', price = '" + transactionUpdate.price + "', ";
                    transactionQuery += "purchase_price = '" + transactionUpdate.purchase_price + "', shipping_id = '" + transactionUpdate.shipping + "', date = '" + transactionUpdate.date + "' ";
                    transactionQuery += "WHERE offer_number = " + transactionUpdate.offer_number + ";";

                    sql.execute(transactionQuery);

                    string customerQuery = "UPDATE customers SET ";
                    customerQuery += "first_name = '" + customerUpdate.first_name + "', last_name = '" + customerUpdate.last_name + "', adress = '" + customerUpdate.adress + "', ";
                    customerQuery += "area_code = '" + customerUpdate.area_code + "', city = '" + customerUpdate.city + "', email = '" + customerUpdate.email + "', ";
                    customerQuery += "ebay_username = '" + customerUpdate.ebay_username + "' ";
                    customerQuery += "WHERE customer_id = " + transactionUpdate.customer_id + ";";

                    sql.execute(customerQuery);
                    break;

                case DialogResult.Cancel:
                    editTransaction.Close();
                    break;
            }
        }

        public void callAboutEcomerci()
        {
            aboutEcomerci about = new aboutEcomerci();
            about.ShowDialog();
        }

#endregion

        

    }
}
